﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 用户表表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class UsersDAO
    {
        #region 添加用户
        /// <summary>
        /// 添加一个新的用户, 返回受影响行数
        /// </summary>
        /// <param name="user">要添加的用户对象</param>
        /// <returns>受影响行数</returns>
        public static int Insert(User user)
        {
            string sql = @"INSERT INTO [Blog_Users]
                                    (
                                    user_login,
                                    user_nickname,
                                    user_password,
                                    user_email,
                                    user_url,
                                    user_regdate,
                                    user_photo,
                                    user_status,
                                    user_role,
                                    user_cdkey,
                                    user_description
                                    )
                                    VALUES
                                    (
                                        @Login,
                                        @NickName,
                                        @Password,
                                        @Email,
                                        @Url,
                                        @RegDate,
                                        @Avatar,
                                        @Status,
                                        @Role,
                                        @CDKey,
                                        @Description
                                    );";
            return ExecuteNonQuery(user, sql);
        }
        #endregion

        #region 删除用户

        #region 删除一条
        /// <summary>
        /// 根据用户ID删除数据库用户表中的用户对象, 返回受影响行数
        /// </summary>
        /// <param name="userid">要删除的用户ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int userid)
        {
            string sql = "DELETE FROM [Blog_Users] WHERE [user_id] = @Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", userid));
        }
        #endregion

        #region 删除多条
        /// <summary>
        /// 根据用户ID组合字符串删除数据库用户表中的用户对象, 返回受影响行数
        /// </summary>
        /// <param name="ids">要删除的用户ID组合字符串, 以逗号分割"1,2,3......"</param>
        /// <returns>受影响行数</returns>
        public static int Delete(string ids)
        {
            //还是有问题的  问题不大  暂且这么用吧, 版本更新在换参数化
            string sql = "DELETE FROM [Blog_Users] WHERE [user_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新用户
        /// <summary>
        /// 根据用户对象的ID属性更新用户信息, 返回受影响行数
        /// </summary>
        /// <param name="user">要更新的用户对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(User user)
        {
            string sql = @"UPDATE [Blog_Users]
                                    SET
                                        [user_login]=@Login,
                                        [user_nickname]=@NickName,
                                        [user_password]=@Password,
                                        [user_email]=@Email,
                                        [user_url]=@Url,
                                        [user_regdate]=@RegDate,
                                        [user_avatar]=@Avatar,
                                        [user_status]=@Status,
                                        [user_role]=@Role,
                                        [user_cdkey]=@CDKey,
                                        [user_description]=@Description
                                    WHERE 
                                        [user_id]=@Id;";
            return ExecuteNonQuery(user, sql);
        }
        #endregion

        #region 选取用户

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("user_id", "Blog_Users");
        }
        #endregion

        #region 检查用户名是否存在
        /// <summary>
        /// 判断用户名是否存在,如果存在则返回TRUE,否则返回FALSE
        /// </summary>
        /// <param name="userName">要判断的用户名</param>
        /// <returns>存在则返回TRUE,否则返回FALSE</returns>
        public static bool Exists(string userName)
        {
            string sql = "SELECT count([user_id]) FROM [Blog_Users] WHERE [user_login]=@userName;";
            //return (int)SqlHelper.ExecuteScalar(sql, new SqlParameter("@userName", userName)) > 0;
            //避免拆箱操作, 性能.. 你懂的..
            return !object.ReferenceEquals(SqlHelper.ExecuteScalar(sql, new SqlParameter("@userName", userName)), null);
        }
        #endregion

        #region 获取用户密码
        /// <summary>
        /// 获取用户密码字符串,存在返回密码,如果不存在则返回null;
        /// </summary>
        /// <param name="userName">要查询的用户名</param>
        /// <returns>查询到的密码</returns>
        public static string SelectPassword(string userName)
        {
            string sql = "SELECT [user_password] FROM [Blog_Users] WHERE [user_login]=@userName;";
            return SqlHelper.ExecuteScalar(sql, new SqlParameter("@userName", userName)) as string;
        }
        #endregion

        #region 获取所有用户信息
        /// <summary>
        /// 获取用户信息表中所有用户信息, 返回对象集合
        /// </summary>
        /// <returns>所有用户信息集合</returns>
        public static List<User> SelectUsers()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [user_id],
                                        [user_login],
                                        [user_nickname],
                                        [user_password],
                                        [user_email],
                                        [user_url],
                                        [user_regdate],
                                        [user_avatar],
                                        [user_status],
                                        [user_role],
                                        [user_cdkey],
                                        [user_description]
                                    FROM
                                        [Blog_Users];";
            return Select(sql);
        }
        #endregion

        #region 根据用户ID获取所有用户对象
        /// <summary>
        /// 根据用户ID获取用户信息表中用户信息对象, 返回用户信息对象
        /// </summary>
        /// <returns>用户信息对象</returns>
        public static User SelectUser(int userid)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [user_id],
                                        [user_login],
                                        [user_nickname],
                                        [user_password],
                                        [user_email],
                                        [user_url],
                                        [user_regdate],
                                        [user_avatar],
                                        [user_status],
                                        [user_role],
                                        [user_cdkey],
                                        [user_description]
                                    FROM
                                        [Blog_Users]
                                    WHERE
                                        [user_id]=@Id;";
            List<User> list = Select(sql, new SqlParameter("@Id", userid));
            return list.Count > 0 ? list[0] : null;
        }
        #endregion

        #endregion

        #region 私有执行方法

        #region Select
        /// <summary>
        /// 私有选择方法
        /// </summary>
        /// <param name="sql">选择语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>数据集合</returns>
        private static List<User> Select(string sql, params SqlParameter[] parameter)
        {
            List<User> list = new List<User>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    User user = new User(
                        reader.GetInt32(0),
                        reader.GetString(1),
                        reader.GetString(2),
                        reader.GetString(3),
                        reader.IsDBNull(4) ? null : reader.GetString(4),
                        reader.IsDBNull(5) ? null : reader.GetString(5),
                        reader.GetDateTime(6).ToString(),
                        reader.GetString(7),
                        reader.GetInt32(8),
                        reader.GetInt32(9),
                        reader.IsDBNull(10) ? null : reader.GetString(10),
                        reader.IsDBNull(11) ? null : reader.GetString(11));
                    list.Add(user);
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有方法执行写入数据库操作
        /// </summary>
        /// <param name="user"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static int ExecuteNonQuery(User user, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Login", user.Login),
                new SqlParameter("@NickName", user.NickName),
                new SqlParameter("@Password", user.Password),
                new SqlParameter("@Email", string.IsNullOrEmpty(user.Email) ? System.DBNull.Value : (object)user.Email),
                new SqlParameter("@Url", string.IsNullOrEmpty(user.Url) ? System.DBNull.Value : (object)user.Url),
                new SqlParameter("@RegDate", string.IsNullOrEmpty(user.RegDate) ? System.DateTime.Now.ToString() : user.RegDate),
                new SqlParameter("@Avatar", user.Avatar),
                new SqlParameter("@Status", user.Status),
                new SqlParameter("@Role", user.Role),
                new SqlParameter("@CDKey", string.IsNullOrEmpty(user.CDKey) ? System.DBNull.Value : (object)user.CDKey),
                new SqlParameter("@Description", string.IsNullOrEmpty(user.Description) ? System.DBNull.Value : (object)user.Description),
                new SqlParameter("@Id", user.Id)
                );
        }
        #endregion

        #endregion
    }
}
